from flask import Flask, render_template, request, jsonify, send_file
import pandas as pd
import datetime
from collections import defaultdict
import io

app = Flask(__name__)

# 星座定义
ZODIAC_SIGNS = [
    {'name': '摩羯座', 'start': (1, 1), 'end': (1, 19)},
    {'name': '水瓶座', 'start': (1, 20), 'end': (2, 18)},
    {'name': '双鱼座', 'start': (2, 19), 'end': (3, 20)},
    {'name': '白羊座', 'start': (3, 21), 'end': (4, 19)},
    {'name': '金牛座', 'start': (4, 20), 'end': (5, 20)},
    {'name': '双子座', 'start': (5, 21), 'end': (6, 20)},
    {'name': '巨蟹座', 'start': (6, 21), 'end': (7, 22)},
    {'name': '狮子座', 'start': (7, 23), 'end': (8, 22)},
    {'name': '处女座', 'start': (8, 23), 'end': (9, 22)},
    {'name': '天秤座', 'start': (9, 23), 'end': (10, 22)},
    {'name': '天蝎座', 'start': (10, 23), 'end': (11, 21)},
    {'name': '射手座', 'start': (11, 22), 'end': (12, 31)}
]

CHINESE_ZODIAC = [
    '鼠', '牛', '虎', '兔', '龙', '蛇', '马', '羊', '猴', '鸡', '狗', '猪'
]

def extract_info_from_id_card(id_card):
    if len(id_card) != 18 or not id_card[:-1].isdigit() or (id_card[-1] not in '0123456789X'):
        raise ValueError(f"无效的身份证号: {id_card}")

    gender_code = int(id_card[16])
    birth_year = int(id_card[6:10])
    birth_month = int(id_card[10:12])
    birth_day = int(id_card[12:14])

    gender = '女' if gender_code % 2 == 0 else '男'
    birth_date = f"{birth_month:02d}-{birth_day:02d}"
    age = datetime.datetime.now().year - birth_year
    
    # 假设籍贯通过前六位确定
    region_code = id_card[:6]
    region_map = {
        '11': '北京市', '12': '天津市', '13': '河北省', '14': '山西省', '15': '内蒙古自治区',
        '21': '辽宁省', '22': '吉林省', '23': '黑龙江省', '31': '上海市', '32': '江苏省',
        '33': '浙江省', '34': '安徽省', '35': '福建省', '36': '江西省', '37': '山东省',
        '41': '河南省', '42': '湖北省', '43': '湖南省', '44': '广东省', '45': '广西壮族自治区',
        '46': '海南省', '50': '重庆市', '51': '四川省', '52': '贵州省', '53': '云南省',
        '54': '西藏自治区', '61': '陕西省', '62': '甘肃省', '63': '青海省', '64': '宁夏回族自治区',
        '65': '新疆维吾尔自治区'
    }
    region = region_map.get(region_code[:2], '未知地区')

    # 计算星座
    def get_zodiac_sign(month, day):
        for sign in ZODIAC_SIGNS:
            start_month, start_day = sign['start']
            end_month, end_day = sign['end']
            if (month == start_month and day >= start_day) or (month == end_month and day <= end_day):
                return sign['name']
        return '摩羯座' if (month == 12 and day >= 22) or (month == 1 and day <= 19) else ''

    zodiac_sign = get_zodiac_sign(birth_month, birth_day)

    # 计算生肖
    chinese_zodiac_index = (birth_year - 4) % 12
    chinese_zodiac = CHINESE_ZODIAC[chinese_zodiac_index]

    return {'gender': gender, 'birthDate': birth_date, 'age': age, 'region': region, 'zodiacSign': zodiac_sign, 'chineseZodiac': chinese_zodiac}


class StudentManagementSystem:
    def __init__(self):
        self.students = []  # 初始时不包含任何学生数据
        self.exams = []

    def add_student(self, name, id_card):
        info = extract_info_from_id_card(id_card)
        new_student = {'id': len(self.students) + 1, 'name': name, 'idCard': id_card, **info, 'scores': {}}
        self.students.append(new_student)

    def delete_student(self, student_id):
        self.students = [student for student in self.students if student['id'] != student_id]

    def update_student(self, student_id, name, id_card):
        for student in self.students:
            if student['id'] == student_id:
                info = extract_info_from_id_card(id_card)
                student.update({'name': name, 'idCard': id_card, **info})
                break

    def add_exam(self, exam_name, exam_time):
        new_exam = {'id': len(self.exams) + 1, 'name': exam_name, 'time': exam_time}
        self.exams.append(new_exam)

    def delete_exam(self, exam_id):
        self.exams = [exam for exam in self.exams if exam['id'] != exam_id]

    def update_exam(self, exam_id, exam_name, exam_time):
        for exam in self.exams:
            if exam['id'] == exam_id:
                exam.update({'name': exam_name, 'time': exam_time})
                break

    def set_score(self, student_id, exam_id, course_name, score):
        for student in self.students:
            if student['id'] == student_id:
                if exam_id not in student['scores']:
                    student['scores'][exam_id] = {}
                student['scores'][exam_id][course_name] = float(score)
                break

    def save_all_scores(self):
        print("所有成绩已保存:", self.students)

    def calculate_statistics(self, selected_exams):
        stats = {}

        for exam in selected_exams:
            stats[exam['name']] = {
                'subjects': defaultdict(lambda: {'total': 0, 'count': 0, 'max': -float('inf'), 'min': float('inf')}),
                'total_students': len(self.students)
            }

            for student in self.students:
                scores = student['scores'].get(exam['id'], {})
                for subject, score in scores.items():
                    stats[exam['name']]['subjects'][subject]['total'] += score
                    stats[exam['name']]['subjects'][subject]['count'] += 1
                    stats[exam['name']]['subjects'][subject]['max'] = max(stats[exam['name']]['subjects'][subject]['max'], score)
                    stats[exam['name']]['subjects'][subject]['min'] = min(stats[exam['name']]['subjects'][subject]['min'], score)

            for subject, data in stats[exam['name']]['subjects'].items():
                data['average'] = data['total'] / data['count']

        return stats

    def generate_report(self, selected_exams):
        if not selected_exams:
            print('请至少选择一个考试来生成报告')
            return

        report_data = []
        for student in self.students:
            student_report = {'name': student['name'], 'idCard': student['idCard'], 'totalScore': 0, 'scores': {}}
            for exam in selected_exams:
                scores = student['scores'].get(exam['id'], {})
                student_report['scores'][exam['name']] = scores
                student_report['totalScore'] += sum(scores.values())
            report_data.append(student_report)

        report_data.sort(key=lambda x: -x['totalScore'])
        for i, student in enumerate(report_data, start=1):
            student['rank'] = i

        df = pd.DataFrame(report_data)
        excel_buffer = io.BytesIO()
        df.to_excel(excel_buffer, index=False)
        excel_buffer.seek(0)
        return excel_buffer

    def upload_students(self, file_stream):
        try:
            df = pd.read_excel(file_stream)
            if list(df.columns) != ['姓名', '身份证号']:
                raise ValueError("Excel 文件必须仅包含 '姓名' 和 '身份证号' 列")
            
            for _, row in df.iterrows():
                name = row['姓名']
                id_card = str(row['身份证号']).strip()
                if len(id_card) != 18 or not id_card[:-1].isdigit() or (id_card[-1] not in '0123456789X'):
                    raise ValueError(f"无效的身份证号: {id_card}")
                self.add_student(name, id_card)
        except Exception as e:
            raise e

    def upload_scores(self, file_stream, exam_id):
        df = pd.read_excel(file_stream)
        exam = next((e for e in self.exams if e['id'] == exam_id), None)
        if not exam:
            raise ValueError(f"Exam with ID {exam_id} not found")

        for _, row in df.iterrows():
            student_name = row['姓名']
            student = next((s for s in self.students if s['name'] == student_name), None)
            if not student:
                continue

            for col in df.columns:
                if col not in ['姓名', '身份证号']:
                    score = row[col]
                    if pd.notna(score):
                        self.set_score(student['id'], exam_id, col, score)


system = StudentManagementSystem()


@app.route('/')
def index():
    return render_template('index.html', students=system.students, exams=system.exams)


@app.route('/students')
def students():
    return render_template('students.html', students=system.students)


@app.route('/exams')
def exams():
    return render_template('exams.html', exams=system.exams)


@app.route('/scores')
def scores():
    return render_template('scores.html', students=system.students, exams=system.exams)


@app.route('/analysis')
def analysis():
    return render_template('analysis.html', exams=system.exams)


@app.route('/add_student', methods=['POST'])
def add_student():
    name = request.form['name']
    id_card = request.form['idCard']
    system.add_student(name, id_card)
    return jsonify(system.students)


@app.route('/delete_student/<int:student_id>', methods=['DELETE'])
def delete_student(student_id):
    system.delete_student(student_id)
    return jsonify(system.students)


@app.route('/update_student', methods=['POST'])
def update_student():
    student_id = int(request.form['studentId'])
    name = request.form['name']
    id_card = request.form['idCard']
    system.update_student(student_id, name, id_card)
    return jsonify(system.students)


@app.route('/add_exam', methods=['POST'])
def add_exam():
    exam_name = request.form['examName']
    exam_time = request.form['examTime']
    system.add_exam(exam_name, exam_time)
    return jsonify(system.exams)


@app.route('/delete_exam/<int:exam_id>', methods=['DELETE'])
def delete_exam(exam_id):
    system.delete_exam(exam_id)
    return jsonify(system.exams)


@app.route('/update_exam', methods=['POST'])
def update_exam():
    exam_id = int(request.form['id'])
    name = request.form['name']
    time = request.form['time']
    system.update_exam(exam_id, name, time)
    return jsonify(system.exams)


@app.route('/set_score', methods=['POST'])
def set_score():
    student_id = int(request.form['studentId'])
    exam_id = int(request.form['examId'])
    course_name = request.form['courseName']
    score = request.form['score']
    system.set_score(student_id, exam_id, course_name, score)
    return jsonify(system.students)


@app.route('/calculate_statistics', methods=['POST'])
def calculate_statistics():
    selected_exams = []
    exam_ids = request.form.getlist('examIds[]')
    for exam_id in exam_ids:
        exam = next((e for e in system.exams if e['id'] == int(exam_id)), None)
        if exam:
            selected_exams.append(exam)
    stats = system.calculate_statistics(selected_exams)
    return jsonify(stats)


@app.route('/generate_report', methods=['POST'])
def generate_report():
    selected_exams = []
    exam_ids = request.form.getlist('examIds[]')
    for exam_id in exam_ids:
        exam = next((e for e in system.exams if e['id'] == int(exam_id)), None)
        if exam:
            selected_exams.append(exam)
    report = system.generate_report(selected_exams)
    if report:
        return send_file(report, as_attachment=True, download_name='report.xlsx')
    else:
        return jsonify({'message': '未选择考试，无法生成报告'})


@app.route('/upload_students', methods=['POST'])
def upload_students():
    file = request.files['file']
    system.upload_students(file.stream)
    return jsonify(system.students)


@app.route('/upload_scores', methods=['POST'])
def upload_scores():
    file = request.files['file']
    exam_id = int(request.form['examId'])
    system.upload_scores(file.stream, exam_id)
    return jsonify(system.students)


if __name__ == '__main__':
    app.run(debug=True)